package com.cs.code.helper;

import com.cs.code.exception.CodeException;
import com.cs.code.model.CsColumn;
import com.cs.code.model.CsIndex;
import com.cs.code.model.CsPrimaryKey;
import com.cs.code.model.CsTable;
import com.cs.code.type.DriverType;
import com.cs.code.type.JdbcMysqlType;
import com.cs.code.type.JdbcPgType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.*;

/**
 * <p>
 *    jdbc工具类
 * </p>
 * @author wind
 * @date    2024/12/28 18:59
 * @version v1.0
 */
public class JdbcHelper {

    private final static Logger logger = LoggerFactory.getLogger(JdbcHelper.class);

    private final String jdbcUrl;

    private final String driver;

    private final Properties props = new Properties();

    private final Map<String, String> typeMap = new HashMap<>(0);

    public final static String UNDERLINE = "_";


    public JdbcHelper(String jdbcUrl, String username, String password) {
        this.jdbcUrl = jdbcUrl;
        DriverType driverType = DriverType.getDriver(jdbcUrl);
        this.driver = driverType.getType();
        props.setProperty("useInformationSchema", "true");
        props.setProperty("user", username);
        props.setProperty("password", password);
        if(DriverType.MYSQL.equals(driverType)){
            this.typeMap.putAll(JdbcMysqlType.toMap());
        }else if(DriverType.POSTGRES.equals(driverType)){
            this.typeMap.putAll(JdbcPgType.toMap());
        }
    }


    /**
     * 获取数据库连接
     * @return
     */
    public Connection getConn(){
        Connection conn;
        try {
            Class.forName(this.driver);
            conn = DriverManager.getConnection(this.jdbcUrl, props);
        } catch (Exception e) {
            logger.error("JdbcHelper.getConn failed", e);
            throw new CodeException("db connection failed", e);
        }
        return conn;
    }



    /**
     * 获取元数据信息
     * @param callBack
     */
    private void exec(DbCallBack callBack){
        Connection con = null;
        try {
            con = getConn();
            DatabaseMetaData db = con.getMetaData();
            callBack.call(db);
        } catch (SQLException e) {
            logger.error("JdbcHelper.exec failed", e);
        } finally {
            close(con);
        }
    }

    /**
     * close
     * @param con
     */
    private void close(Connection con){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                logger.error("JdbcHelper.close failed", e);
            }
        }
    }


    /**
     * 获取数据库实例名称
     * @return
     */
    public String getCatalog(){
        int end = jdbcUrl.length();
        int index = jdbcUrl.indexOf("?");
        if(index > -1){
            end = index;
        }
        String s = jdbcUrl.substring(0 ,end);
        int start = s.lastIndexOf("/");
        bool(start == -1, "jdbc url格式不合法");
        return jdbcUrl.substring(start + 1, end);
    }

    /**
     * 获取数据库类型
     * @return
     */
    public String getDbType(){
        String url = jdbcUrl.trim();
        String prefix = "jdbc:";
        bool(!url.startsWith(prefix), "jdbc url格式不合法");
        int start = prefix.length();
        int end = jdbcUrl.indexOf(":", start);
        bool(end == -1, "jdbc url格式不合法");
        return jdbcUrl.substring(start, end);
    }

    /**
     * 获取所有数据库实例
     * @return
     */
    public List<String> getDbs(){
        List<String> dbs = new ArrayList<>();
        exec(db -> {
            ResultSet rs = db.getCatalogs();
            while(rs.next()){
                String dbName = rs.getString("TABLE_CAT");
                dbs.add(dbName);
            }
            rs.close();
        });
        return dbs;
    }

    /**
     * 获取所有数据库实例
     * @return
     */
    public List<String> getSchemas(String catalog){
        return getSchemas(catalog, null);
    }

    /**
     * 获取所有数据库实例
     * @return
     */
    public List<String> getSchemas(String catalog, String schema){
        List<String> schemas = new ArrayList<>();
        exec(db -> {
            String pattern = schema;
            if(isNotBlank(schema)){
                pattern = "%" + schema + "%";
            }
            ResultSet rs = db.getSchemas(catalog, pattern);
            while(rs.next()){
                String dbName = rs.getString("TABLE_SCHEM");
                schemas.add(dbName);
            }
            rs.close();
        });
        return schemas;
    }


    /**
     * 获取数据库所有表
     * @return
     */
    public List<CsTable> getTables(){
        return getTables(getCatalog());
    }

    /**
     * 获取数据库所有表
     * @param catalog
     * @return
     */
    public List<CsTable> getTables(String catalog){
        return getTables(catalog, null, null);
    }


    /**
     * 获取数据库表的描述信息
     * @param catalog
     * @param schema
     * @return
     */
    public List<CsTable> getTables(String catalog, String schema){
        return getTables(catalog, schema, null);
    }

    /**
     * 获取数据库表的描述信息
     * @param catalog
     * @param schema
     * @param tableName
     * @return
     */
    public List<CsTable> getTables(String catalog, String schema, String tableName) {
        Connection con = null;
        List<CsTable> tables = new ArrayList<>();
        try {
            con = getConn();
            DatabaseMetaData db = con.getMetaData();
            if(isNotBlank(schema)){
                schema = "%" + schema + "%";
            }else{
                schema = null;
            }

            if(isNotBlank(tableName)){
                tableName = "%" + tableName + "%";
            }else{
                tableName = null;
            }
            ResultSet rs = db.getTables(catalog, schema, tableName, new String[]{"TABLE"});
            while(rs.next()) {
                tables.add(getTable(db, schema, rs));
            }
            rs.close();
        } catch (SQLException e) {
            logger.error("JdbcHelper.getTables failed", e);
        } finally {
            close(con);
        }
        return tables;
    }

    /**
     * 组装table数据
     * @param rs
     * @return
     * @throws Exception
     */
    private CsTable getTable(DatabaseMetaData db, String schema, ResultSet rs) {
        if(rs != null){
            try {
                CsTable table = new CsTable();
                List<String> imports = new ArrayList<>();
                String tableName = rs.getString("TABLE_NAME");
                table.setName(tableName);
                table.setAlias(getCamelCase(tableName, false));
                table.setProperty(getCamelCase(tableName, true));
                String catalog = rs.getString("TABLE_CAT");
                table.setCatalog(catalog);
                table.setRemarks(rs.getString("REMARKS"));
                List<CsPrimaryKey> primaryList = getPrimaryKeys(db, catalog, tableName);
                table.setKeys(primaryList);
                Map<String, Boolean> primaryMap = new HashMap<>(primaryList.size());
                primaryList.forEach(primary -> {
                    primaryMap.put(primary.getName(), primary.isAuto());
                });
                List<CsColumn> columnList = getColumns(db, catalog, schema, tableName);
                columnList.forEach(c -> {
                    String javaType = c.getJavaType();
                    if(!javaType.startsWith("java.lang") && !imports.contains(javaType)){
                        imports.add(javaType);
                    }
                    String name = c.getName();
                    Boolean auto = primaryMap.get(name);
                    if(auto != null){
                        c.setPk(true);
                        c.setAuto(auto);
                        if(primaryList.size() == 1){
                            table.setPrimary(c);
                        }
                    }
                });
                table.setImports(imports);
                table.setColumns(columnList);
                return table;
            } catch (Exception e) {
                logger.error("JdbcHelper.getTable failed", e);
            }
        }
        return null;
    }

    /**
     * 获取数据库列信息
     * @param catalog
     * @param tableName
     * @return
     */
    public List<CsColumn> getColumns(String catalog, String tableName){
        List<CsColumn> columns = new ArrayList<>();
        exec(db -> {
            try {
                columns.addAll(getColumns(db, catalog, "%", tableName));
            } catch (Exception e) {
                logger.error("JdbcHelper.getColumns failed}", e);
            }
        });
        return columns;
    }

    /**
     * 获取数据库列信息
     * @param catalog
     * @param schema
     * @param tableName
     * @return
     */
    public List<CsColumn> getColumns(String catalog, String schema, String tableName){
        List<CsColumn> columns = new ArrayList<>();
        exec(db -> {
            try {
                columns.addAll(getColumns(db, catalog, schema, tableName));
            } catch (Exception e) {
                logger.error("JdbcHelper.getColumns failed}", e);
            }
        });
        return columns;
    }

    /**
     * 组装column数据
     * @param db
     * @param catalog
     * @param schema
     * @param tableName
     * @return
     * @throws SQLException
     * @throws Exception
     */
    private List<CsColumn> getColumns(DatabaseMetaData db, String catalog, String schema, String tableName) throws SQLException{
        List<CsColumn> columns = new ArrayList<>();
        if(isEmpty(schema)){
            schema = "%";
        }
        ResultSet rs = db.getColumns(catalog, schema, tableName, "%");
        while (rs.next()){
            CsColumn column = new CsColumn();
            String colName = rs.getString("COLUMN_NAME");
            String typeName = rs.getString("TYPE_NAME");
            column.setName(colName);
            column.setType(typeName);
            String javaType = getJavaType(typeName);
            column.setJavaType(javaType);
            String[] typeArr = javaType.split("\\.");
            if(typeArr.length > 0){
                column.setClassType(typeArr[typeArr.length - 1]);
            }
            column.setAlias(getCamelCase(colName, false));
            column.setProperty(getCamelCase(colName, true));
            column.setSize(rs.getInt("COLUMN_SIZE"));
            column.setNullable(rs.getInt("NULLABLE"));
            column.setRemarks(rs.getString("REMARKS"));
            column.setDigits(rs.getInt("DECIMAL_DIGITS"));

            columns.add(column);
        }
        rs.close();
        return columns;
    }

    /**
     * 获取数据库列信息
     * @param catalog
     * @param tableName
     * @return
     */
    public List<CsIndex> getIndexs(String catalog, String tableName){
        return getIndexs(catalog, null, tableName);
    }

    /**
     * 获取数据库列信息
     * @param catalog
     * @param schema
     * @param tableName
     * @return
     */
    public List<CsIndex> getIndexs(String catalog, String schema, String tableName){
        List<CsIndex> indexList = new ArrayList<>();
        exec(db -> {
            try {
                indexList.addAll(getIndexs(db, schema, catalog, tableName));
            } catch (Exception e) {
                logger.error("JdbcHelper.getIndexs failed}", e);
            }
        });
        return indexList;
    }

    /**
     * 组装索引数据
     * TABLE_NAME：表名。
     * INDEX_NAME：索引名。
     * COLUMN_NAME：索引列名。
     * NON_UNIQUE：是否非唯一索引（true 表示非唯一，false 表示唯一）。
     * INDEX_QUALIFIER：索引的限定符（通常是数据库名或模式名）。
     * TYPE：索引类型（如主键、唯一索引等）。
     * ORDINAL_POSITION：列在索引中的位置。
     * ASC_OR_DESC：列的排序方向（A 表示升序，D 表示降序）。
     * CARDINALITY：索引的基数（即索引中不同值的数量）。
     * PAGES：索引所占用的页数。
     * FILTER_CONDITION：过滤条件（如果适用）
     * @param db
     * @param catalog
     * @param tableName
     * @return
     * @throws SQLException
     * @throws Exception
     */
    private List<CsIndex> getIndexs(DatabaseMetaData db, String catalog, String schema, String tableName) throws SQLException{
        List<CsIndex> indexList = new ArrayList<>();
        ResultSet rs = db.getIndexInfo(catalog, schema, tableName, false, false);
        while (rs.next()){
            CsIndex index = new CsIndex();
            index.setName(rs.getString("INDEX_NAME"));
            index.setTableName(rs.getString("TABLE_NAME"));
            index.setColumnName(rs.getString("COLUMN_NAME"));
            index.setUnique(rs.getBoolean("NON_UNIQUE"));
            index.setIndexQualifier(rs.getString("INDEX_QUALIFIER"));
            index.setType(rs.getString("TYPE"));
            index.setPosition(rs.getString("ORDINAL_POSITION"));
            index.setSort(rs.getString("ASC_OR_DESC"));
            index.setPage(rs.getInt("PAGES"));
            index.setCondition(rs.getString("FILTER_CONDITION"));
            index.setCardinality(rs.getInt("CARDINALITY"));
            indexList.add(index);
        }
        rs.close();
        return indexList;
    }


    /**
     * TABLE_CAT String => 表类别（可为 null）
     * TABLE_SCHEMA String => 表模式（可为 null）
     * TABLE_NAME String => 表名称
     * COLUMN_NAME String => 列名称
     * KEY_SEQ short => 主键中的序列号
     * PK_NAME String => 主键的名称（可为 null）
     * @param catalog
     * @param tableName
     * @return
     */
    public List<CsPrimaryKey> getPrimaryKeys(String catalog, String tableName){
        List<CsPrimaryKey> keys = new ArrayList<>();
        exec(db -> keys.addAll(getPrimaryKeys(db, catalog, tableName)));
        return keys;
    }

    /**
     * 组装primaryKey数据
     * @param db
     * @param catalog
     * @param tableName
     * @return
     */
    private List<CsPrimaryKey> getPrimaryKeys(DatabaseMetaData db, String catalog, String tableName){
        List<CsPrimaryKey> keys = new ArrayList<>();
        try {
            ResultSet rs = db.getPrimaryKeys(catalog, null, tableName);
            while(rs.next()) {
                CsPrimaryKey primaryKey = new CsPrimaryKey();
                primaryKey.setName(rs.getString("COLUMN_NAME"));
                primaryKey.setPkName(rs.getString("PK_NAME"));
                primaryKey.setKeySeq(rs.getShort("KEY_SEQ"));

                String isAutoIncrement = getString(rs, "IS_AUTOINCREMENT");
                String extra = getString(rs,"EXTRA"); // MySQL特有
                primaryKey.setIsAutoIncrement(isAutoIncrement);
                primaryKey.setExtra(extra);
                boolean autoIncrement = "YES".equalsIgnoreCase(isAutoIncrement) ||
                        (extra != null && extra.toLowerCase().contains("auto_increment"));
                primaryKey.setAuto(autoIncrement);
                keys.add(primaryKey);
            }
            rs.close();
        } catch (SQLException e) {
            logger.error("JdbcHelper.getPrimaryKeys failed", e);
        }
        return keys;
    }

    /**
     * 获取ResultSet的值，有异常则记录错误日志并返回空
     * @param rs
     * @param name
     * @return
     */
    private String getString(ResultSet rs, String name){
        try {
            return rs.getString(name);
        } catch (SQLException e) {
//            logger.error("JdbcHelper.getString failed", e);
        }
        return null;
    }

    /**
     * 数据库连接测试
     * @return
     */
    public boolean ping(){
        try (Connection conn = getConn()){
            PreparedStatement ps = conn.prepareStatement("select 1");
            return ps.execute();
        } catch (Exception e) {
            logger.error("JdbcHelper.ping failed", e);
            throw new CodeException("db ping failed", e);
        }
    }

    /**
     * 获取java type
     * @param jdbcType
     * @return
     */
    private String getJavaType(String jdbcType){
        if(isEmpty(jdbcType)){
            throw new CodeException("获取数据库字段类型失败,值为空");
        }
        jdbcType = jdbcType.replaceAll(" UNSIGNED", "").toUpperCase().trim();
        String val = this.typeMap.get(jdbcType);
        if(isEmpty(val)){
            throw new CodeException(jdbcType + "数据类型暂未支持");
        }
        return val;
    }

    /**
     * 判断字符串是否为null或空字符串
     * @param str
     * @return
     */
    public boolean isEmpty(String str){
        return str == null || "".equals(str.trim());
    }

    /**
     * 判断字符串不为null且不为空字符串
     * @param str
     * @return
     */
    public boolean isNotBlank(String str){
        return !isEmpty(str);
    }


    /**
     * 驼峰命名 hello_world => HelloWorld
     * @param name 数据库列名
     * @param isFirst 首字母小写为false helloWorld， 大写为true HelloWorld
     * @return
     */
    private String getCamelCase(String name, boolean isFirst){
        StringBuilder sb = new StringBuilder();
        if(isNotBlank(name)){
            String[] strArr = name.split(UNDERLINE);
            for(int i = 0; i < strArr.length; i++){
                String s = strArr[i];
                if(i == 0){
                    sb.append(getCap(s, isFirst));
                }else{
                    sb.append(getCap(s, true));
                }
            }
        }
        return sb.toString();
    }

    /**
     * 将单词首字母变大小写
     * @param str
     * @param flag true变大写， false变小写
     * @return
     */
    private String getCap(String str, boolean flag){
        StringBuilder sb = new StringBuilder();
        int length = str != null ? str.length() : 0;
        if(length >= 1){
            if(flag){
                sb.append(str.substring(0, 1).toUpperCase());
            }else{
                sb.append(str.substring(0, 1).toLowerCase());
            }
            if(length > 1){
                sb.append(str.substring(1));
            }
        }
        return sb.toString();
    }

    /**
     * 表达式校验
     * @param flag
     * @param msg
     */
    public void bool(boolean flag, String msg){
        if(flag){
            throw new IllegalArgumentException(msg);
        }
    }

    /**
     * @package com.cs.core.callback
     * @className DbCallBack
     * @note 数据库元数据回调
     * @author wind
     * @date 2020/12/9 20:04
     */
    @FunctionalInterface
    public interface DbCallBack {

        /**
         * 数据库元数据操作
         * @param db
         * @throws SQLException
         */
        void call(DatabaseMetaData db) throws SQLException;
    }
}
